Introduction to Visual Basic for Applications

Getting Started

The first thing you will need to do is to get the 'Developer' ribbon displayed on Excel.

Open up Excel and Click on the File tab

Under the file tab select Options

Select Customize Ribbon and then the check box for Developer and press OK

You will then see the DEVELOPER ribbon on Excel, which you should select.

Then by clicking on the Visual Basic button, you will see the Visual Basic Editor as below

This is where you write your code

Your First Program

Select the excel button in the top left hand corner of the visual basic screen to bring back the excel spreadsheet

Click on the Insert Button and then select Command Button from the ActiveX Controls

Click on the surface of the workbook where you want the button to appear

Design Mode should now be selected. If it is then clicking on the button (you have just created) will select it. If Design Mode is off then clicking on the button will operate it and you will see the normal button behaviour

Make sure Design Mode is selected and double click the button you have made. This takes you back into the Visual Basic editor. This time some code is written for you.

Private Sub CommandButton1_Click()
End Sub

Between the two lines of code type the following code:

  MsgBox ("Hello World")

Then go back to excel, switch Design Mode OFF and click on your button and see what happens

A message box should appear with the message "Hello World"

Congratulations - you have written your first computer program

Modules and Functions

In the VBA editor find the window that says 'Project VBAProject'. This is where you control the whole project from.

As we wrote out code having double clicked the button on the excel spreadsheet then the code was stored 'behind the button relating to that sheet'

If you expand Microsoft Excel Objects and then double click on Sheet1 (sheet1) you will see the code you have entered

However suppose we want to write some code that all the sheets in the spreadsheet can 'see'. In this case we need to write the code in a Module.

Right click on the 'Project VBAProject' window and select Insert > Module. A new module called Module1 will be created.

Type the following code:

function square_number(x as double) as double
  answer = x * x
  square_number = answer
 end function

Now type the numbers 1 to 10 into cells A1 to A10 and then put the formula =square_number(A1) into cell B2 and drag it down to B10. You will see that your formula has worked and calculated the squares of each of the numbers from 1 to 10.

Can you understand what each of the lines in the above program are doing

Locating Cells

Using a function argument is one way we can send information from excel to VBA and visa-versa. However there are many others

Clear your spreadsheet (but leave your function) and label the cell C3 as "topleft". Also label the range of cells from C3 to F6 as "matrix"

Now enter some random numbers into each of the cells in the range: "matrix"

Put a new button on the screen, double click it and enter the following code

cells(3,9)=[topleft]
cells(4,9)=[topleft].offset(2,1)
cells(5,9)=[matrix].cells(1,1)
cells(6,9)=[matrix].cells(2,1)
cells(7,9)=cells(3,3)
cells(8,9)=Range("matrix").cells(2,1)
cells(9,9)=Range("topleft").offset(2,1)
cells(10,9)=Range("matrix").columns.count
cells(11,9)=Range("matrix").rows.count

Can you see what each of these instruction is doing when you run the code

Try inserting a column of cells between A and B. Then run the code again. Can you explain what has happened

Variable Types

We have already seen double used as a variable definition

There are many different variable types in VBA but the main ones we need to worry about are:

A more detailed description is given on the msdn website

VBA is generally quite good at guessing which type of variable you are using

However it is best practice to start all modules with the line option explicit, which forces you to define each of your variables before using them

Go back to Module1 and type option explicit above your function.

Now try and use your function: square_number again.

You will see that your function no longer works. The solution is to type:

Dim answer as double

on the line below the function declaration

what do you think this line of code is doing

It is saying 'dimension the variable called: answer, as a double precision floating point number'

option explicit has forced you to defined all your variables in your code

Function and Subs

We have already seen one example of a function and a sub

Can you explain the difference

A function returns an answer whereas a sub does not

If you want a set of tasks performing but do not want a result returned then use a sub.

Just because they are automatically created when you create a button does not mean that you can only use them behind a button you can use them any other time you wish.

Functions can be used to return values to excel or to other functions or to subs. We will see examples of this under loops.

Both functions and subs can have parameters as we saw with our square_number function earlier on

function square_number(x as double) as double

x is sent to the function and is defined as type double. This means that it does not have to be redefined even if you are using "option explicit"

Functions: other issues

Public or Private

Functions can be Public or Private. A public function is visible outside of its own module whereas a private one can only be seen within the module in which it is written. Public is the default and this is what we use most of the time as we wish to use functions from excel and from worksheet modules.

Using excel functions in VBA

Some functions such as 'normdist' are excel functions but not VBA functions. These can be accessed from VBA by using the following syntax:

p = Application.NormSDist(2)

Is the probability that a normal random variable is less than 2

Application.Pi()

returns a very accurate value of $\pi$

Log vs Ln

In VBA we use the function Log to give us the natural logarithm (to the base e). Ln is not defined in VBA

RAND() excel vs RND() VBA

Random numbers in VBA are generated with the function RND(). This produces a random number between 0 and 1 each time it is called

Inline functions

VBA can also take a function name as an argument to a function and then use that function within another function. We will see this in operation later in the course. The syntax is as follows:

x = Run ("square_number", 6)

Runs the square_number function with the parameter 6. This feature will be used later in the course when we write integration routines to which we which to pass different functions to integrate

Loops

For Next Loop

Suppose we wanted to produce a simple function to calculate factorials

Using the formula $factorial(x)=\prod\limits_{r=1}^{x}{r}$

We will need a loop to calculate this function

In VBA we code this type of loop with a For Next loop like so:

prod = 1
for r = 1 to x
  prod = prod * r
 next r

Do Loops

Sometimes we don't know in advance how many times we wish to loop through the code until we have looped through it

In these circumstances we can use a Do Loop, where the condition can either be at the start or the end of the loop

Do {while | Until} condition
  statement1
  statement2
  ...
 Loop

Do
  statement1
  statement2
  ...
 Loop {while | Until} condition

Arrays

You can set up array in VBA and you can also pass arrays from excel ranges

Consider the following code:

Function sum_matrix(m) As Double
Dim sum As Double
Dim i As Integer
Dim j As Integer
sum = 0
For i = 1 To m.Rows.Count
  For j = 1 To m.Columns.Count
    sum = sum + m(i, j)
   Next j
 Next i
sum_matrix = sum
End Function

Type some numbers into a range of cells in Excel then put the function:

=sum_matrix("B3:D7") or whatever your range of cells is and you will see the result is that the your range has been converted into an array in VBA and all the elements have been summed

Write a function to multiply two square matrices together in VBA and then return the sum of the elements of the new matrix. Now you will need to pass two matrices to VBA. You will also need to define a new array within VBA for which you use the following code:

Dim new_matrix() as double
n = input_matrix.rows.count
ReDim new_matrix(n,n)

In this code the Dim statement sets up the matrix (but with no dimensions) and says the values are double. The next line then finds out how big the matrices being multiplied are. The third statement then resizes the output array to the size required for the multiplication. There is no particular logic as to why the Dim and ReDim statements cannot be combined - it is just how VBA works

You may alsio wish to write some code to multiply two matrices and return the answer matrix to excel. You will need to do this with a sub as function cannot communicate directly with an excel spreadsheet. Here is some example code which does this.

Conditional Statements

If statements can be used to execute some code on one condition and other code on another condition

The following code will return true if x is divisible by d and false otherwise

Note the use of a boolean variable as a return value and the Mod function, which provides modulo arithmetic

Function is_div(x, d As Integer) As Boolean
  If x Mod d = 0 Then
    is_div = True
   Else
    is_div = False
   End If
End Function

See what happens when you put the numbers in your matrix through this function

Another way of executing code conditionally is by using the Case statement

Function grouping(x As Double) As String
Select Case x
Case Is < 5
  grouping = "small group"
Case Is < 15
  grouping = "middle group"
Case Else
  grouping = "big group"
End Select
End Function

See what happens when you put the numbers in your matrix through this function

Excel Objects

Excel VBA is an object-oriented language, like C++

Each Excel object represents a feature or a piece of functionality in Excel.

Examples: Workbooks, Worksheets, Ranges, Charts and even Excel itself (the Application object)

Your programs in VBA manipulate the properties and apply methods to Excel objects.

Each Excel object has values or properties associated with it, and also functions called 'methods' that operate on the object and its values and properties.

The objects in Excel can either be singular, or come in collections, and are organized in tree-like hierarchy.

At the top level is the 'Excel program' itself; it is called the 'Application' object. It is a singular object: Excel has only one Application object.

Then come the 'workbooks', stored in the 'Workbooks Collection' which consists of all open workbooks.

Each workbook has a collection of worksheets or Sheets (all the sheets in a workbook).

Each sheet has the collection of cells, called 'Ranges' and possibly 'Charts' in the 'Chart collection'.

Assessing parts uses the dot notation:

Application.Ln("A1")
Application.Workbooks("Book1.xls")
Sheets("Sheet1").Range("F4:F9")

You don't always need to the full hierarchy of names if the object with the 'focus', for example the current or 'Active sheet', is intended.

Sheets("Sheet1").Range("F4:F9")
ActiveWorkbook.Sheets("Sheet1").Range("F4:F9")

or even

ActiveSheet.Range("F4:F9")
Range("F4:F9")

Object properties:

Objects have properties, which could be attributes of the object, or values or settings of the object.

Application.ScreenUpdating = False
Range("A1").Name = "Year"
Range("A1").Value = 2006

Object methods:

They also have methods, which are a set of predefined activities that an object can carry out or have applied to it.

Sheets("Sheet1").Delete
Range("A1:A10").Select
Range("A1:A10").Copy
Range("B1:B10").PasteSpecial

There are many web sites offering VBA help just Google what you want to know and the answers should be there

Complex Numbers

To use complex numbers in VBA we make use of the OOP (object orientated programming) features

VBA is not a natural OOP language is the same way that Delphi or C++ are, but objects can still be used

To see how OOP works we are going to look at manipulating complex numbers. Enter the following code into your Module

Type cnum
  x As Double
  y As Double
 End Type

Function add_cnum(ByRef one As cnum, ByRef two As cnum) As cnum
  add_cnum.x = one.x + two.x
  add_cnum.y = one.y + two.y
End Function

Function set_cnum(ax, ay As Double) As cnum
  set_cnum.x = ax
  set_cnum.y = ay
End Function

Sub do_calc()
  Dim c1 As cnum
  Dim c2 As cnum
  Dim c3 As cnum
  c1 = set_cnum(Cells(1, 1), Cells(1, 2))
  c2 = set_cnum(Cells(2, 1), Cells(2, 2))
  c3 = add_cnum(c1, c2)
  Cells(3, 1) = c3.x
  Cells(3, 2) = c3.y
End Sub

Enter two complex numbers into the cells A1:B1 and A2:B2

Click the Run Macro button in Excel, select do_calc and you will see the two complex numbers added together in the cells A3:B3

There are a number of features of this code:

• A new type (or object) cnum is created
• This new type has two variables within it: a and b and so is in total a complex number
• This new 'object' can be passed as a parameter to functions and returned as a result from functions.

The following algebra will help:

Multiply

$(x_1 + iy_1) \times (x_2 + iy_2) = x_1 x_2 - y_1 y_2 + i(y_1 x_2 + x_1 y_2)$

Divide

To divide two complex numbers you will need to use the complex conjugate

$\frac{1}{x+iy} = \frac{x-iy}{(x+iy)\times(x-iy)} = \frac{x-iy}{x^2+y^2}$

Exponential

To calculate the exponential you will need Euler's Formula:

$e^{ix}=cos x + i sin x$

Logarithm

To calculate the logarithm you will need to make $x_2$ and $y_2$ the subjects of the formula, starting from: $x_1 + iy_1 = e^{x_2 + iy_2}$ and again applying Euler's formula and matching terms in real and imaginary parts

Power

To calculate a power you need to start with $a=b^c$ and follow the logic below

$$a=b^c$$

$$ln(a) = c\times ln(b)$$

$$a=exp(c\times ln(b))$$

Excel file with solutions to above complex number calculations. To be accessed when you have made a good attempt yourself